package dao;

import pojo.Bill;
import utils.DBUtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Date;

/**
 * @author konley
 * @date 2020-06-10 22:41
 */
public class BillDao {
    //根据sid查账单
    public List<Bill> getBillsBySid(int sid){
        Connection con = DBUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Bill> bills = new ArrayList<Bill>();
        try {
            int cid = new CardDao().getCid(sid);
            ps = con.prepareStatement("select * from bill where cid=?");
            ps.setInt(1,cid);
            rs = ps.executeQuery();
            while (rs.next()){
                Bill b = new Bill();
                b.setBid(rs.getInt("bid"));
                b.setCid(rs.getInt("cid"));
                b.setType(rs.getString("type"));
                b.setMoney(rs.getInt("money"));
                b.setAddress(rs.getString("address"));
                b.setTime(rs.getString("time"));
                bills.add(b);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(rs,ps,con);
        }
        return bills;
    }
    //根据sid查账单
    public List<Bill> getBillsByCid(int cid){
        Connection con = DBUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Bill> bills = new ArrayList<Bill>();
        try {
            ps = con.prepareStatement("select * from bill where cid=?");
            ps.setInt(1,cid);
            rs = ps.executeQuery();
            while (rs.next()){
                Bill b = new Bill();
                b.setBid(rs.getInt("bid"));
                b.setCid(rs.getInt("cid"));
                b.setType(rs.getString("type"));
                b.setMoney(rs.getInt("money"));
                b.setAddress(rs.getString("address"));
                b.setTime(rs.getString("time"));
                bills.add(b);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(rs,ps,con);
        }
        return bills;
    }

    //添加（充值）账单
    public boolean billAdd(int cid, int money){
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection con = DBUtils.getConnection();
        String type="充值";
        String address = "发卡室";

        //获取当前时间 util.date
        Date date = new Date();
        //转为sql.date
        java.sql.Date sqlDate = new java.sql.Date(date.getTime());
        String time = sqlDate+"";   //获取到的日期
        try{
            ps = con.prepareStatement("insert into bill value(null,?,?,?,?,?)");
            ps.setInt(1,cid);
            ps.setString(2,type);
            ps.setInt(3,money);
            ps.setString(4,address);
            ps.setString(5,time);
            int num = ps.executeUpdate();
            if(num > 0){
                return true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(ps,con);
        }
        return false;
    }

    //删除该cid下的账单
    public boolean deleteBillByCid(int cid){
        PreparedStatement ps = null;
        Connection con = DBUtils.getConnection();
        try{
            ps = con.prepareStatement("delete  from bill where cid=?");
            ps.setInt(1,cid);
            int num = ps.executeUpdate();
            if(num > 0){
                return true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(ps,con);
        }
        return false;
    }
}
